package 书店销售管理系统;

import java.awt.EventQueue;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class SalesManagement extends JFrame {
    private static final long serialVersionUID = 1L;

    private JPanel contentPane;
    private JTable table;
    private JTextField bookIdTextField;
    private JTextField memberIdTextField;
    private JTextField saleTimeTextField;
    private JTextField saleAmountTextField;
    private JTextField salePriceTextField;
    private JTextField BNAMETextField;
    private JComboBox<String> comboBox;

    // 数据库连接属性
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;

    // 表格模型
    private DefaultTableModel model;

    public SalesManagement() {
        setTitle("图书销售管理系统");
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setBounds(100, 100, 800, 600);
        contentPane = new JPanel();
        contentPane.setLayout(null);
        contentPane.setBorder(null);
        setContentPane(contentPane);

        // 创建标签
        JLabel bookIdLabel = new JLabel("图书编号：");
        bookIdLabel.setBounds(10, 10, 80, 25);
        contentPane.add(bookIdLabel);

        JLabel BNAMELabel = new JLabel("图书名称：");
        BNAMELabel.setBounds(10, 45, 80, 25);
        contentPane.add(BNAMELabel);

        JLabel memberIdLabel = new JLabel("会员编号：");
        memberIdLabel.setBounds(10, 80, 80, 25);
        contentPane.add(memberIdLabel);

        JLabel saleTimeLabel = new JLabel("销售时间：");
        saleTimeLabel.setBounds(10, 115, 80, 25);
        contentPane.add(saleTimeLabel);

        JLabel saleAmountLabel = new JLabel("销售数量：");
        saleAmountLabel.setBounds(10, 150, 80, 25);
        contentPane.add(saleAmountLabel);

        JLabel salePriceLabel = new JLabel("销售价格：");
        salePriceLabel.setBounds(10, 175, 80, 25);
        contentPane.add(salePriceLabel);

        // 创建文本框
        bookIdTextField = new JTextField();
        bookIdTextField.setBounds(100, 10, 150, 25);
        contentPane.add(bookIdTextField);
        bookIdTextField.setColumns(10);

        BNAMETextField = new JTextField();
        BNAMETextField.setBounds(100, 45, 150, 25);
        contentPane.add(BNAMETextField);
        BNAMETextField.setColumns(10);

        memberIdTextField = new JTextField();
        memberIdTextField.setBounds(100, 80, 150, 25);
        contentPane.add(memberIdTextField);
        memberIdTextField.setColumns(10);

        saleTimeTextField = new JTextField();
        saleTimeTextField.setBounds(100, 115, 150, 25);
        contentPane.add(saleTimeTextField);
        saleTimeTextField.setColumns(10);

        saleAmountTextField = new JTextField();
        saleAmountTextField.setBounds(100, 150, 150, 25);
        contentPane.add(saleAmountTextField);
        saleAmountTextField.setColumns(10);

        salePriceTextField = new JTextField();
        salePriceTextField.setBounds(100, 175, 150, 25);
        contentPane.add(salePriceTextField);
        salePriceTextField.setColumns(10);

        // 创建按钮
        JButton addButton = new JButton("售出");
        addButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                doAdd();
            }
        });
        addButton.setBounds(300, 10, 80, 25);
        contentPane.add(addButton);

        JButton searchButton = new JButton("查询");
        searchButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                doSearch();
            }
        });
        searchButton.setBounds(300, 45, 80, 25);
        contentPane.add(searchButton);

        JButton deleteButton = new JButton("退货");
        deleteButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                doDelete();
            }
        });
        deleteButton.setBounds(300, 80, 80, 25);
        contentPane.add(deleteButton);

        JButton bestsellerButton = new JButton("畅销书");
        bestsellerButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                doBestseller();
            }
        });
        bestsellerButton.setBounds(400, 10, 80, 25);
        contentPane.add(bestsellerButton);
        
        
     // 创建菜单栏
        JMenuBar menuBar = new JMenuBar();
        setJMenuBar(menuBar);

        // 添加下拉菜单
        JMenu dropDownMenu = new JMenu("菜单");
        menuBar.add(dropDownMenu);

     

        // 添加子菜单：会员信息管理
        JMenuItem memberItem = new JMenuItem("会员信息管理");
        memberItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 在此添加跳转到“会员信息管理”界面的代码
                    new MemberManagement().setVisible(true);
            }
        });
        dropDownMenu.add(memberItem);
         
        // 添加子菜单：会员类别管理
        JMenuItem memberItem1 = new JMenuItem("会员类别管理");
        memberItem1.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 在此添加跳转到“会员类别管理”界面的代码
                    new VipTypeManagement().setVisible(true);
            }
        });
        dropDownMenu.add(memberItem1);
        
     // 添加子菜单：销售信息管理
        JMenuItem salesItem1 = new JMenuItem("图书信息管理");
        salesItem1.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 在此添加跳转到“销售信息管理”界面的代码
                    new Book().setVisible(true);
            }
        });
        dropDownMenu.add(salesItem1);
        
     // 添加子菜单：用户信息管理
        JMenuItem memberItem11 = new JMenuItem("用户信息管理");
        memberItem11.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // 在此添加跳转到“会员类别管理”界面的代码
                    new UserManagement().setVisible(true);
            }
        });
        dropDownMenu.add(memberItem11);
        
        // 添加子菜单：退出
        JMenuItem exitItem = new JMenuItem("退出");
        exitItem.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                dispose(); // 关闭窗口
                System.exit(0); // 退出应用程序
            }
        });
        dropDownMenu.add(exitItem);
        

        // 创建下拉菜单
        comboBox = new JComboBox<String>();
        comboBox.setModel(new DefaultComboBoxModel<String>(new String[] { "按天查询", "按月查询" }));
        comboBox.setBounds(500, 10, 100, 25);
        contentPane.add(comboBox);

        // 创建表格
        JScrollPane scrollPane = new JScrollPane();
        scrollPane.setBounds(10, 200, 764, 351);
        contentPane.add(scrollPane);

        table = new JTable();
        scrollPane.setViewportView(table);

        // 初始化表格模型
        model = new DefaultTableModel();
        model.setColumnIdentifiers(new Object[] { "图书编号", "图书名称","会员编号","销售时间", "销售量","销售价格",});
        table.setModel(model);
    }

    /**
     * 添加销售信息
     */
    private void doAdd() {
        try {
            // 获取输入的销售信息
            String bookId = bookIdTextField.getText();
            String BNAME = BNAMETextField.getText();
            String memberId = memberIdTextField.getText();
            String saleTime = saleTimeTextField.getText();
            int saleAmount = Integer.parseInt(saleAmountTextField.getText());
            double salePrice = Double.parseDouble(salePriceTextField.getText());

            // 获取当前日期
            Date now = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(now);
            String currentDate = null;

            // 获取数据库连接
            conn = Oracleconnection.getConnection();

            // 创建SQL语句
            String sql = "INSERT INTO SELL (BNO, BNAME,VNO, TIME, SELL_NUMBER, SPRICE) " + "VALUES (?, ?, ?, ?, ?, ?)";

            // 创建PreparedStatement对象，执行添加SQL语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bookId);
            pstmt.setString(2, BNAME);
            pstmt.setString(3, memberId);
            pstmt.setString(4, saleTime);
            pstmt.setLong(5, saleAmount);
            pstmt.setDouble(6, salePrice);

            int result = pstmt.executeUpdate();

            // 判断添加是否成功
            if (result > 0) {
                JOptionPane.showMessageDialog(null, "添加成功", "提示", JOptionPane.INFORMATION_MESSAGE);
                // 刷新表格数据
                doSearch();
            } else {
                JOptionPane.showMessageDialog(null, "添加失败", "错误", JOptionPane.ERROR_MESSAGE);
            }
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "添加失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            // 释放资源
            release(conn, pstmt, rs);
        }
    }
    /**
     * 查询销售信息
     */
    private void doSearch() {
        try {
            // 获取数据库连接
            conn = Oracleconnection.getConnection();

            // 创建SQL语句
            String sql = "SELECT * FROM SELL ORDER BY BNO DESC";

            // 创建PreparedStatement对象，执行查询SQL语句
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            // 清空表格数据
            model.setRowCount(0);

            // 遍历结果集，将数据添加到表格模型中
            while (rs.next()) {
                Object[] rowData = { rs.getString("BNO"), rs.getString("BNAME"), rs.getString("VNO"),rs.getString("TIME"),rs.getString("SELL_NUMBER"),rs.getString("SPRICE") };
                model.addRow(rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            // 释放资源
            release(conn, pstmt, rs);
        }
    }

    /**
     * 删除销售记录
     */
    private void doDelete() {
        try {
            // 获取输入的图书编号和销售时间
            String bookId = bookIdTextField.getText();
            String saleTime = saleTimeTextField.getText();

            // 获取数据库连接
            conn = Oracleconnection.getConnection();

            // 创建SQL语句，执行删除操作
            String sql = "DELETE FROM SELL WHERE BNO = ? AND TIME = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bookId);
            pstmt.setString(2, saleTime);
            int result = pstmt.executeUpdate();

            // 判断删除是否成功
            if (result > 0) {
                JOptionPane.showMessageDialog(null, "删除成功", "提示", JOptionPane.INFORMATION_MESSAGE);
                // 刷新表格数据
                doSearch();
            } else {
                JOptionPane.showMessageDialog(null, "删除失败", "错误", JOptionPane.ERROR_MESSAGE);
            }

        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "删除失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE); } finally {   release(conn, pstmt, rs); } }

/**
 * 查询畅销书
 */
private void doBestseller() {
    try {
        // 获取选择的查询类型
        String type = (String) comboBox.getSelectedItem();
        // 如果选择按天查询，则需要输入日期
        if (type.equals("按天查询")) {
            String date = JOptionPane.showInputDialog(null, "请输入要查询的日期（格式为YYYY-MM-DD）", "输入日期", JOptionPane.QUESTION_MESSAGE);
            if (date == null) {
                return;
            }
            // 获取某天销售量最大的书
            String bookName = getBestsellerByDay(date);
            if (bookName != null) {
                JOptionPane.showMessageDialog(null, date + "销售量最高的书为：" + bookName, "畅销书", JOptionPane.INFORMATION_MESSAGE);
            } else {
                JOptionPane.showMessageDialog(null, date + "没有销售记录", "畅销书", JOptionPane.INFORMATION_MESSAGE);
            }
        } else {
            // 如果选择按月或按季查询，则需要输入月份或季度
            String period = JOptionPane.showInputDialog(null, "请输入要查询的" + type, "输入查询条件", JOptionPane.QUESTION_MESSAGE);
            if (period == null) {
                return;
            }
            // 获取某月或某季度销售量前三的书
            List<String> bookNames = getBestsellersByMonthOrQuarter(type, period);
            if (bookNames.size() > 0) {
                StringBuilder sb = new StringBuilder();
                sb.append(period);
                sb.append(type.equals("按月查询") ? "月" : "季度");
                sb.append("销售量前三的书为：\n");
                for (int i = 0; i < bookNames.size(); i++) {
                    sb.append(i+1);
                    sb.append(". ");
                    sb.append(bookNames.get(i));
                    sb.append("\n");
                }
                JOptionPane.showMessageDialog(null, sb.toString(), "畅销书", JOptionPane.INFORMATION_MESSAGE);
            } else {
                JOptionPane.showMessageDialog(null, period + type + "没有销售记录", "畅销书", JOptionPane.INFORMATION_MESSAGE);
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "查询失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
    } finally {
        // 释放资源
        release(conn, pstmt, rs);
    }
}

/**
 * 获取某天销售量最大的书名
 */
private String getBestsellerByDay(String date) throws SQLException {
    // 获取数据库连接
    conn = Oracleconnection.getConnection();

    // 创建SQL语句，查询某天销售量最大的书名
    String sql = "SELECT BNAME FROM (SELECT BNAME, SUM(SELL_NUMBER) SELLNUM FROM SELL WHERE TIME = ? GROUP BY BNAME ORDER BY SELLNUM DESC) WHERE ROWNUM <= 1";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, date);
    rs = pstmt.executeQuery();

    if (rs.next()) {
        // 如果查询结果不为空，则返回销售量最大的书名
        return rs.getString("BNAME");
    } else {
        // 如果查询结果为空，则返回null
        return null;
    }
}

/**
 * 获取某月或某季度销售量前三的书名列表
 */
private List<String> getBestsellersByMonthOrQuarter(String type, String period) throws SQLException {
    // 获取数据库连接
    conn = Oracleconnection.getConnection();

    // 创建SQL语句，查询某月或某季度销售量前三的书名列表
    String sql = "SELECT BNAME FROM (SELECT BNAME, SUM(SELL_NUMBER) SELLNUM FROM SELL WHERE TIME LIKE ? GROUP BY BNAME ORDER BY SELLNUM DESC) WHERE ROWNUM <= 3";
    pstmt = conn.prepareStatement(sql);
    if (type.equals("按月查询")) {
        // 如果按月查询，则查询某月的销售记录，例如“2021-08-01%"
        pstmt.setString(1, period + "-01%");
    } else {
        // 如果按季度查询，则查询某季度的销售记录，例如“2021-01-01~2021-03-31”
        int year = Integer.parseInt(period.substring(0, 4));
        int quarter = Integer.parseInt(period.substring(5));
        String startDate = "";
        String endDate = "";
        switch (quarter) {
            case 1:
                startDate = "01-01";
                endDate = "03-31";
                break;
            case 2:
                startDate = "04-01";
                endDate = "06-30";
                break;
            case 3:
                startDate = "07-01";
                endDate = "09-30";
                break;
            case 4:
                startDate = "10-01";
                endDate = "12-31";
                break;
        }
        pstmt.setString(1, year + "-" + startDate + "~" + year + "-" + endDate + "%");
    }
    rs = pstmt.executeQuery();

    // 将查询结果添加到列表中
    List<String> bookNames = new ArrayList<>();
    while (rs.next()) {
        bookNames.add(rs.getString("BNAME"));
    }
    return bookNames;
}

/**
 * 释放资源
 */
private void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
    try {
        if (rs != null) {
            rs.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable() {
        public void run() {
            try {
            	SalesManagement frame = new SalesManagement();
                frame.setVisible(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
}
}